টেবিল এবং ডাটা ম্যানিপুলেশন

Apache POI ব্যবহার করে আপনি Excel এবং Word ডকুমেন্টে টেবিল তৈরি এবং ডেটা ম্যানিপুলেট করতে পারবেন। এখানে আমরা Excel এবং Word ডকুমেন্টে টেবিল তৈরি এবং ডেটা পরিবর্তন করার কিছু সাধারণ উদাহরণ দেখব।

1. Excel ফাইলের টেবিল এবং ডাটা ম্যানিপুলেশন

Excel ফাইলের টেবিল তৈরি এবং ডেটা ম্যানিপুলেশন করতে Apache POI এর HSSF (XLS) এবং XSSF (XLSX) লাইব্রেরি ব্যবহার করা হয়।

উদাহরণ: Excel ফাইলে টেবিল তৈরি এবং ডেটা ম্যানিপুলেশন

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelTableManipulationExample {
    public static void main(String[] args) throws IOException {
        // XSSFWorkbook দিয়ে নতুন Excel ফাইল তৈরি
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        // টেবিলের হেডার তৈরি
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Name");
        headerRow.createCell(1).setCellValue("Age");
        headerRow.createCell(2).setCellValue("City");

        // ডেটা প্রবেশ করা
        Object[][] data = {
            {"John", 25, "New York"},
            {"Anna", 30, "London"},
            {"Mike", 35, "Sydney"}
        };

        // ডেটা সেল যোগ করা
        int rowNum = 1;
        for (Object[] dataRow : data) {
            Row row = sheet.createRow(rowNum++);
            int cellNum = 0;
            for (Object field : dataRow) {
                Cell cell = row.createCell(cellNum++);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }
        }

        // ফাইল সেভ করা
        try (FileOutputStream fileOut = new FileOutputStream("example_table.xlsx")) {
            workbook.write(fileOut);
        }

        workbook.close();
        System.out.println("Excel ফাইল সফলভাবে সেভ হয়েছে!");
    }
}

এটি কী করবে:

• একটি XLSX ফাইল তৈরি করবে।
• প্রথম রোতে Name, Age, এবং City শিরোনাম দিয়ে টেবিলের হেডার তৈরি করবে।
• পরবর্তি রোতে ৩টি ব্যক্তির নাম, বয়স এবং শহরের ডেটা প্রবেশ করবে।

2. Word ডকুমেন্টে টেবিল তৈরি এবং ডেটা ম্যানিপুলেশন

Word ডকুমেন্টে টেবিল তৈরি এবং ডেটা ম্যানিপুলেট করার জন্য Apache POI এর XWPF লাইব্রেরি ব্যবহার করা হয়।

উদাহরণ: Word ডকুমেন্টে টেবিল তৈরি এবং ডেটা ম্যানিপুলেশন

import org.apache.poi.xwpf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;

public class WordTableManipulationExample {
    public static void main(String[] args) throws IOException {
        // XWPFDocument দিয়ে নতুন Word ডকুমেন্ট তৈরি
        XWPFDocument document = new XWPFDocument();

        // টেবিল তৈরি
        XWPFTable table = document.createTable();

        // টেবিলের প্রথম রো তৈরি এবং হেডার যোগ করা
        XWPFTableRow row = table.getRow(0);
        row.getCell(0).setText("Name");
        row.addNewTableCell().setText("Age");
        row.addNewTableCell().setText("City");

        // ডেটা যোগ করা
        Object[][] data = {
            {"John", 25, "New York"},
            {"Anna", 30, "London"},
            {"Mike", 35, "Sydney"}
        };

        for (Object[] dataRow : data) {
            row = table.createRow();
            row.getCell(0).setText((String) dataRow[0]);
            row.getCell(1).setText(String.valueOf(dataRow[1]));
            row.getCell(2).setText((String) dataRow[2]);
        }

        // ফাইল সেভ করা
        try (FileOutputStream out = new FileOutputStream("example_table.docx")) {
            document.write(out);
        }

        document.close();
        System.out.println("Word ডকুমেন্ট সফলভাবে সেভ হয়েছে!");
    }
}

এটি কী করবে:

• একটি Word (.docx) ফাইল তৈরি করবে।
• একটি টেবিল তৈরি করে তাতে Name, Age, এবং City শিরোনাম যুক্ত করবে।
• এরপর তিনটি সারিতে বিভিন্ন ডেটা (নাম, বয়স, শহর) যোগ করবে।

সারাংশ

Apache POI ব্যবহার করে Excel এবং Word ফাইলের মধ্যে টেবিল তৈরি এবং ডেটা ম্যানিপুলেশন খুবই সহজ। আপনি XSSF (Excel ফাইলের জন্য) এবং XWPF (Word ডকুমেন্টের জন্য) লাইব্রেরি ব্যবহার করে টেবিল তৈরি, ডেটা যুক্ত, এবং সেগুলির মান পরিবর্তন করতে পারেন। এতে রিপোর্ট তৈরির কাজ অনেক সহজ এবং দ্রুত হয়, যা ব্যবসায়িক ডেটা প্রসেসিং এবং ডকুমেন্ট প্রসেসিংয়ে অত্যন্ত কার্যকর।

Apache POI ব্যবহার করে Excel ফাইলে টেবিল তৈরি এবং ডেটা ইনসার্ট করা খুবই সহজ। এখানে আমরা দেখব কীভাবে HSSF বা XSSF (অর্থাৎ .xls এবং .xlsx) ফরম্যাটে টেবিল তৈরি এবং ডেটা ইনসার্ট করা যায়। টেবিল তৈরি করতে হলে আপনাকে প্রথমে একটি শীট (sheet) তৈরি করতে হবে, তারপর সেল (cell) তৈরি করে ডেটা ইনসার্ট করতে হবে। এরপর সেই সেলগুলোকে একটি টেবিলের অংশ হিসেবে ফরম্যাট করতে হবে।

Excel এ টেবিল তৈরি এবং ডেটা ইনসার্ট করার ধাপ

1. XSSF ব্যবহার করে (.xlsx) ফরম্যাটে টেবিল তৈরি করা

এখানে .xlsx ফরম্যাটে টেবিল তৈরি এবং ডেটা ইনসার্ট করার উদাহরণ দেওয়া হয়েছে:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelTableExample {

    public static void main(String[] args) throws IOException {
        // WorkBook তৈরি
        XSSFWorkbook workbook = new XSSFWorkbook();
        
        // শীট তৈরি
        Sheet sheet = workbook.createSheet("Employee Data");

        // টেবিলের হেডার রো তৈরি
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Employee ID");
        headerRow.createCell(1).setCellValue("Name");
        headerRow.createCell(2).setCellValue("Position");
        headerRow.createCell(3).setCellValue("Salary");

        // টেবিলের ডেটা ইনসার্ট করা
        Object[][] employeeData = {
            {1, "John Doe", "Manager", 75000},
            {2, "Jane Smith", "Developer", 65000},
            {3, "Mike Johnson", "Designer", 50000},
            {4, "Emily Davis", "Tester", 45000}
        };

        // ডেটার জন্য রো তৈরি এবং ইনসার্ট
        int rowNum = 1;
        for (Object[] rowData : employeeData) {
            Row row = sheet.createRow(rowNum++);
            int cellNum = 0;
            for (Object cellData : rowData) {
                Cell cell = row.createCell(cellNum++);
                if (cellData instanceof String) {
                    cell.setCellValue((String) cellData);
                } else if (cellData instanceof Integer) {
                    cell.setCellValue((Integer) cellData);
                }
            }
        }

        // ফাইল আউটপুট
        try (FileOutputStream fileOut = new FileOutputStream("employee_data.xlsx")) {
            workbook.write(fileOut);
        }

        workbook.close();
    }
}

কোড ব্যাখ্যা:

1. XSSFWorkbook: এটি .xlsx ফরম্যাটের Excel ফাইল তৈরি করতে ব্যবহৃত হয়।
2. createSheet(): একটি নতুন শীট তৈরি করা হচ্ছে "Employee Data" নামের।
3. createRow(): একটি নতুন রো তৈরি করা হচ্ছে। প্রথম রোটি হেডার রো হিসেবে ব্যবহৃত হবে এবং পরবর্তী রোতে ডেটা ইনসার্ট করা হবে।
4. createCell(): প্রতিটি রোতে সেল তৈরি করা হচ্ছে এবং সেলে ডেটা ইনসার্ট করা হচ্ছে।
5. FileOutputStream: এটি Excel ফাইলটিকে আউটপুট ফাইলে সেভ করতে ব্যবহৃত হয়।

2. HSSF ব্যবহার করে (.xls) ফরম্যাটে টেবিল তৈরি করা

এখানে .xls ফরম্যাটে টেবিল তৈরি এবং ডেটা ইনসার্ট করার উদাহরণ দেওয়া হয়েছে:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelTableExampleHSSF {

    public static void main(String[] args) throws IOException {
        // WorkBook তৈরি
        HSSFWorkbook workbook = new HSSFWorkbook();

        // শীট তৈরি
        Sheet sheet = workbook.createSheet("Employee Data");

        // টেবিলের হেডার রো তৈরি
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Employee ID");
        headerRow.createCell(1).setCellValue("Name");
        headerRow.createCell(2).setCellValue("Position");
        headerRow.createCell(3).setCellValue("Salary");

        // টেবিলের ডেটা ইনসার্ট করা
        Object[][] employeeData = {
            {1, "John Doe", "Manager", 75000},
            {2, "Jane Smith", "Developer", 65000},
            {3, "Mike Johnson", "Designer", 50000},
            {4, "Emily Davis", "Tester", 45000}
        };

        // ডেটার জন্য রো তৈরি এবং ইনসার্ট
        int rowNum = 1;
        for (Object[] rowData : employeeData) {
            Row row = sheet.createRow(rowNum++);
            int cellNum = 0;
            for (Object cellData : rowData) {
                Cell cell = row.createCell(cellNum++);
                if (cellData instanceof String) {
                    cell.setCellValue((String) cellData);
                } else if (cellData instanceof Integer) {
                    cell.setCellValue((Integer) cellData);
                }
            }
        }

        // ফাইল আউটপুট
        try (FileOutputStream fileOut = new FileOutputStream("employee_data.xls")) {
            workbook.write(fileOut);
        }

        workbook.close();
    }
}

কোড ব্যাখ্যা:

এটি HSSF ব্যবহার করে .xls ফরম্যাটে Excel ফাইল তৈরি করে। কোডের স্ট্রাকচার এবং কনসেপ্ট আগের XSSF উদাহরণের মতো একই।

3. Excel টেবিল ফরম্যাটিং (Optional)

যদি আপনি টেবিলের সেলগুলির ফরম্যাটিং করতে চান (যেমন, সেলের বর্ডার, ব্যাকগ্রাউন্ড কালার ইত্যাদি), তাহলে CellStyle ব্যবহার করতে পারেন।

উদাহরণ:

// CellStyle তৈরি করা
CellStyle style = workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);

// স্টাইল সেলস এ অ্যাপ্লাই করা
Cell cell = row.createCell(0);
cell.setCellValue("Employee ID");
cell.setCellStyle(style);

এটি সেলগুলিতে সীমানা (border) এবং অ্যালাইনমেন্ট (alignment) সেট করে।

সারাংশ

Apache POI ব্যবহার করে Excel ফাইলের মধ্যে টেবিল তৈরি এবং ডেটা ইনসার্ট করা একটি সহজ প্রক্রিয়া। আপনি XSSF বা HSSF ব্যবহার করে .xlsx বা .xls ফরম্যাটে টেবিল তৈরি করতে পারেন এবং CellStyle ব্যবহার করে সেলগুলির ফরম্যাটিং করতে পারেন। এই প্রক্রিয়া দিয়ে আপনি ডেটাবেস বা অন্যান্য উৎস থেকে ডেটা নিয়ে Excel ফাইল তৈরি করতে পারবেন এবং সেই ফাইলটি ব্যবহারকারীদের জন্য প্রস্তুত করতে পারবেন।

Apache POI লাইব্রেরি ব্যবহার করে আপনি Excel ফাইলের ডেটাতে AutoFilter এবং Sorting প্রয়োগ করতে পারেন। এই দুটি কার্যাবলী ব্যবহার করে আপনি Excel শিটে ডেটা আরও সংগঠিত ও অনুসন্ধানযোগ্য করে তুলতে পারেন।

১. AutoFilter প্রয়োগ করা

AutoFilter হল Excel-এর একটি ফিচার, যা ব্যবহারকারীদের কলাম অনুসারে ডেটা ফিল্টার করতে সাহায্য করে। Apache POI এর মাধ্যমে আপনি Excel শিটের উপর AutoFilter প্রয়োগ করতে পারেন, যা ব্যবহারকারীদের একক বা একাধিক কলাম থেকে ডেটা সিলেক্ট করতে দেয়।

কোড উদাহরণ: Excel ফাইলে AutoFilter প্রয়োগ করা

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class AutoFilterExample {
    public static void main(String[] args) throws IOException {
        // Workbook তৈরি করা
        Workbook workbook = new XSSFWorkbook();
        
        // Sheet তৈরি করা
        Sheet sheet = workbook.createSheet("Sheet1");

        // Header Row তৈরি করা
        Row headerRow = sheet.createRow(0);
        Cell headerCell1 = headerRow.createCell(0);
        headerCell1.setCellValue("Name");

        Cell headerCell2 = headerRow.createCell(1);
        headerCell2.setCellValue("Age");

        Cell headerCell3 = headerRow.createCell(2);
        headerCell3.setCellValue("Department");

        // Data Row তৈরি করা
        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("Alice");
        row1.createCell(1).setCellValue(30);
        row1.createCell(2).setCellValue("HR");

        Row row2 = sheet.createRow(2);
        row2.createCell(0).setCellValue("Bob");
        row2.createCell(1).setCellValue(25);
        row2.createCell(2).setCellValue("Finance");

        Row row3 = sheet.createRow(3);
        row3.createCell(0).setCellValue("Charlie");
        row3.createCell(1).setCellValue(35);
        row3.createCell(2).setCellValue("Engineering");

        // AutoFilter প্রয়োগ করা
        sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 2)); // Header row (row 0) থেকে columns 0 to 2 পর্যন্ত

        // Excel ফাইল সেভ করা
        FileOutputStream fileOut = new FileOutputStream("autoFilterExample.xlsx");
        workbook.write(fileOut);
        fileOut.close();

        // Workbook বন্ধ করা
        workbook.close();

        System.out.println("AutoFilter সফলভাবে প্রয়োগ করা হয়েছে!");
    }
}

কোড ব্যাখ্যা:

• sheet.setAutoFilter(): এই মেথডের মাধ্যমে ফিল্টার সন্নিবেশ করা হয়। এখানে CellRangeAddress(0, 0, 0, 2) দ্বারা হেডার রো (row 0) এবং প্রথম তিনটি কলাম (column 0 থেকে column 2) সিলেক্ট করা হয়।
• Excel শিটের মধ্যে Filter option তখন প্রদর্শিত হবে, যার মাধ্যমে ব্যবহারকারী একটি নির্দিষ্ট কলামে ডেটা ফিল্টার করতে পারবে।

২. Sorting প্রয়োগ করা

Excel-এ Sorting ফিচারটি ব্যবহারকারীদের ডেটাকে নির্দিষ্ট কলাম অনুসারে সাজাতে সাহায্য করে। Apache POI তে ডেটাকে সোর্ট করার জন্য বিশেষ কোনো API সরাসরি নেই, তবে আপনি Java Collections API এবং Excel ফাইলের Row গুলি সঠিকভাবে সাজিয়ে সিলেক্টেড কলামের ডেটা সঠিকভাবে সাজাতে পারেন।

কোড উদাহরণ: Excel ফাইলে Sorting প্রয়োগ করা

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class SortExcelExample {
    public static void main(String[] args) throws IOException {
        // Workbook তৈরি করা
        Workbook workbook = new XSSFWorkbook();
        
        // Sheet তৈরি করা
        Sheet sheet = workbook.createSheet("Sheet1");

        // Data তৈরি করা
        List<RowData> data = new ArrayList<>();
        data.add(new RowData("Alice", 30, "HR"));
        data.add(new RowData("Bob", 25, "Finance"));
        data.add(new RowData("Charlie", 35, "Engineering"));

        // Data সorting করা (Age অনুসারে)
        Collections.sort(data, Comparator.comparingInt(RowData::getAge));

        // Header Row তৈরি করা
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Name");
        headerRow.createCell(1).setCellValue("Age");
        headerRow.createCell(2).setCellValue("Department");

        // Sorted Data শিটে যোগ করা
        int rowNum = 1;
        for (RowData rowData : data) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(rowData.getName());
            row.createCell(1).setCellValue(rowData.getAge());
            row.createCell(2).setCellValue(rowData.getDepartment());
        }

        // Excel ফাইল সেভ করা
        FileOutputStream fileOut = new FileOutputStream("sortedExample.xlsx");
        workbook.write(fileOut);
        fileOut.close();

        // Workbook বন্ধ করা
        workbook.close();

        System.out.println("Sorting সফলভাবে প্রয়োগ করা হয়েছে!");
    }

    // Data structure to store row data
    static class RowData {
        private String name;
        private int age;
        private String department;

        public RowData(String name, int age, String department) {
            this.name = name;
            this.age = age;
            this.department = department;
        }

        public String getName() {
            return name;
        }

        public int getAge() {
            return age;
        }

        public String getDepartment() {
            return department;
        }
    }
}

কোড ব্যাখ্যা:

• Collections.sort(): এখানে Collections.sort() ব্যবহার করা হয়েছে যাতে ডেটাকে বয়স (age) অনুযায়ী সজ্জিত করা যায়।
• RowData: এই কাস্টম ক্লাসে আমরা প্রতিটি রো (row) এর ডেটা সংরক্ষণ করি এবং তারপরে Collections.sort() এর মাধ্যমে সেগুলো সাজানো হয়।

৩. AutoFilter এবং Sorting একসাথে প্রয়োগ করা

যদি আপনি Excel ফাইলে AutoFilter এবং Sorting একসাথে প্রয়োগ করতে চান, তাহলে আপনি উল্লিখিত দুটি কোড কম্বিন করে সেটি করতে পারবেন। উদাহরণস্বরূপ, প্রথমে ডেটা সজ্জিত করুন এবং তারপরে AutoFilter প্রয়োগ করুন।

sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 2)); // Sorting প্রয়োগের পরে AutoFilter প্রয়োগ করুন।

AutoFilter এবং Sorting দুইটি খুবই শক্তিশালী ফিচার যা Excel ডেটার কার্যকারিতা বৃদ্ধি করে। Apache POI লাইব্রেরি ব্যবহার করে:

• আপনি AutoFilter প্রয়োগ করে Excel ফাইলের ডেটা ফিল্টার করতে পারেন।
• আপনি Sorting প্রয়োগ করে Excel ফাইলের ডেটাকে নির্দিষ্ট কলাম অনুযায়ী সাজাতে পারেন।

এই দুটি ফিচার ব্যবহার করে আপনি Excel ফাইলের ডেটাকে আরও ভালোভাবে সংগঠিত এবং পরিচালিত করতে পারবেন।

Apache POI ব্যবহার করে আপনি Excel ফাইলে Data Validation এবং Dropdown Lists তৈরি করতে পারেন। Data Validation আপনাকে Excel শীটে ব্যবহারকারীর ইনপুট সীমাবদ্ধ করার অনুমতি দেয়, যেমন নির্দিষ্ট মান বা ভ্যালিড ডেটা সেটের মধ্যে মান হতে হবে। এর মাধ্যমে আপনি ব্যবহারকারীদের ভুল ইনপুট এড়াতে পারেন। Dropdown Lists এর মাধ্যমে ব্যবহারকারীকে একটি নির্দিষ্ট তালিকা থেকে মান নির্বাচন করতে সাহায্য করা হয়।

এখানে দেখানো হবে কিভাবে আপনি Apache POI ব্যবহার করে Data Validation এবং Dropdown List তৈরি করতে পারেন।

১. Data Validation তৈরি করা

Data Validation ব্যবহারকারীর ইনপুট সীমাবদ্ধ করতে সাহায্য করে, যেমন একটি নির্দিষ্ট সেলের জন্য সংখ্যা বা তারিখ নির্দিষ্ট করা।

উদাহরণ: একটি সেলে সংখ্যা ইনপুটের জন্য Data Validation তৈরি করা

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class DataValidationExample {
    public static void main(String[] args) throws IOException {
        // Excel ওয়ার্কবুক তৈরি করা
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet 1");

        // Data Validation তৈরি
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(
                DataValidationConstraint.OperatorType.BETWEEN,
                "1", "100"); // 1 থেকে 100 এর মধ্যে সংখ্যার জন্য ভ্যালিডেশন
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); // প্রথম সারির প্রথম সেল (A1)
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);

        // একটি সেলে ভ্যালু লেখার উদাহরণ
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Enter a number between 1 and 100");

        // ফাইল সেভ করা
        try (FileOutputStream fileOut = new FileOutputStream(new File("example.xlsx"))) {
            workbook.write(fileOut);
        }

        // ওয়ার্কবুক বন্ধ করা
        workbook.close();
    }
}

ব্যাখ্যা:

• DataValidationHelper: এটি Data Validation তৈরি করার জন্য ব্যবহার করা হয়।
• createNumericConstraint: এটি একটি সংখ্যার জন্য validation তৈরি করতে ব্যবহৃত হয়, যেখানে BETWEEN অপারেটর ব্যবহার করে ১ থেকে ১০০ এর মধ্যে মান থাকা প্রয়োজন।
• CellRangeAddressList: এখানে যেকোনো একটি সেলে (এখানে A1) validation প্রয়োগ করা হয়েছে।
• addValidationData: শীটে validation যোগ করতে ব্যবহৃত হয়।

২. Dropdown List তৈরি করা

Dropdown List তৈরি করার মাধ্যমে ব্যবহারকারীকে একটি নির্দিষ্ট তালিকা থেকে মান নির্বাচন করতে দেওয়া হয়। এটি Data Validation এর একটি উপাংশ হিসেবে কাজ করে।

উদাহরণ: একটি সেলে Dropdown List তৈরি করা

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class DropdownListExample {
    public static void main(String[] args) throws IOException {
        // Excel ওয়ার্কবুক তৈরি করা
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet 1");

        // Data Validation এর মাধ্যমে Dropdown List তৈরি করা
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(
                new String[]{"Option 1", "Option 2", "Option 3", "Option 4"}); // Dropdown options
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); // প্রথম সারির প্রথম সেল (A1)
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);

        // একটি সেলে ভ্যালু লেখার উদাহরণ
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Select an option");

        // ফাইল সেভ করা
        try (FileOutputStream fileOut = new FileOutputStream(new File("example.xlsx"))) {
            workbook.write(fileOut);
        }

        // ওয়ার্কবুক বন্ধ করা
        workbook.close();
    }
}

ব্যাখ্যা:

• createExplicitListConstraint: এটি একটি ড্রপডাউন তালিকা তৈরি করতে ব্যবহৃত হয়। এখানে "Option 1", "Option 2", "Option 3", এবং "Option 4" একটি তালিকা হিসেবে দেয়া হয়েছে।
• CellRangeAddressList: এখানে যেকোনো একটি সেলে (এখানে A1) ড্রপডাউন তালিকা যোগ করা হয়েছে।
• addValidationData: শীটে validation (ড্রপডাউন) যোগ করতে ব্যবহৃত হয়।

৩. Multiple Data Validation এবং Dropdown Lists

একই শীটে একাধিক Data Validation এবং Dropdown List যোগ করাও সম্ভব। এখানে একটি উদাহরণ দেওয়া হচ্ছে যেখানে দুটি সেলে (A1 এবং B1) আলাদা Data Validation ও Dropdown List দেওয়া হবে।

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class MultipleValidationExample {
    public static void main(String[] args) throws IOException {
        // Excel ওয়ার্কবুক তৈরি করা
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet 1");

        // প্রথম সেলে (A1) Data Validation (সংখ্যা 1 থেকে 100 এর মধ্যে)
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(
                DataValidationConstraint.OperatorType.BETWEEN,
                "1", "100");
        CellRangeAddressList addressListA = new CellRangeAddressList(0, 0, 0, 0); // A1 সেল
        DataValidation validationA = dataValidationHelper.createValidation(constraint, addressListA);
        sheet.addValidationData(validationA);

        // দ্বিতীয় সেলে (B1) Dropdown List
        DataValidationConstraint dropdownConstraint = dataValidationHelper.createExplicitListConstraint(
                new String[]{"Option A", "Option B", "Option C"});
        CellRangeAddressList addressListB = new CellRangeAddressList(0, 0, 1, 1); // B1 সেল
        DataValidation validationB = dataValidationHelper.createValidation(dropdownConstraint, addressListB);
        sheet.addValidationData(validationB);

        // A1 এবং B1 সেলে ভ্যালু লেখার উদাহরণ
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("Enter a number between 1 and 100");
        row.createCell(1).setCellValue("Select an option");

        // ফাইল সেভ করা
        try (FileOutputStream fileOut = new FileOutputStream(new File("example_multiple_validation.xlsx"))) {
            workbook.write(fileOut);
        }

        // ওয়ার্কবুক বন্ধ করা
        workbook.close();
    }
}

সারাংশ

• Data Validation: এটি Excel শীটে ইনপুট ডেটা সীমাবদ্ধ করার জন্য ব্যবহৃত হয়। আপনি সংখ্যা, তারিখ, বা লিস্ট এর মাধ্যমে ইনপুট কন্ট্রোল করতে পারেন।
• Dropdown List: এটি Data Validation এর অংশ হিসেবে ব্যবহৃত হয় এবং ব্যবহারকারীকে একটি নির্দিষ্ট তালিকা থেকে নির্বাচন করতে সাহায্য করে।
• Apache POI এর মাধ্যমে সহজেই Excel ফাইলে Data Validation এবং Dropdown Lists তৈরি করতে পারেন, যা ব্যবহারকারীর ভুল ইনপুট থেকে বাঁচতে সাহায্য করে।

এভাবে আপনি Apache POI ব্যবহার করে Excel শীটে Data Validation এবং Dropdown Lists তৈরি করতে পারেন।

Apache POI লাইব্রেরি ব্যবহার করে আপনি Excel ফাইলের Formulas (সূত্র) এবং Functions পরিচালনা করতে পারেন। Excel এ বিভিন্ন ধরণের সূত্র থাকে যা ডেটাকে গাণিতিক বা যুক্তিসম্মতভাবে প্রসেস করে, এবং এই সূত্রগুলো Apache POI এর মাধ্যমে আপনার Java প্রোগ্রাম থেকে ব্যবহার করা সম্ভব।

1. Formula ব্যবহার করা

Formula হল সেই সূত্র যা Excel সেলে গাণিতিক বা লজিক্যাল হিসাব বা প্রসেসিং করতে ব্যবহৃত হয়। Apache POI আপনাকে Excel ফাইলের সেলে Formula সেট করতে এবং পরবর্তী সময়ে সেই Formula এর ফলাফল গ্রহণ করতে সাহায্য করে।

1.1 Formula সেট করা

এখানে একটি উদাহরণ দেওয়া হল, যেখানে একটি সেলে Formula হিসেবে একটি গাণিতিক হিসাব নির্ধারণ করা হয়েছে:

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelFormulaExample {
    public static void main(String[] args) throws IOException {
        // Excel Workbook তৈরি করা
        XSSFWorkbook workbook = new XSSFWorkbook();
        
        // Sheet তৈরি করা
        Sheet sheet = workbook.createSheet("Sheet1");
        
        // Row এবং Cell তৈরি করা
        Row row = sheet.createRow(0);
        Cell cell1 = row.createCell(0);
        Cell cell2 = row.createCell(1);
        Cell cell3 = row.createCell(2);
        
        // কিছু ডেটা যোগ করা
        cell1.setCellValue(10);  // A1 সেলে মান 10
        cell2.setCellValue(20);  // B1 সেলে মান 20
        
        // Formula ব্যবহার করা
        cell3.setCellFormula("A1+B1");  // C1 সেলে A1 এবং B1 এর যোগফল
        
        // Excel ফাইল সেভ করা
        FileOutputStream out = new FileOutputStream("formulaExample.xlsx");
        workbook.write(out);
        out.close();
        
        workbook.close();
        System.out.println("Formula সেট করা সম্পন্ন!");
    }
}

এখানে:

• cell3.setCellFormula("A1+B1"); – এখানে Formula সেট করা হয়েছে যেখানে A1 এবং B1 সেলের মান যোগ করা হবে।

1.2 Formula রেজাল্ট পড়া

Excel ফাইলটি খুলে যখন আপনি Formula এর সেলটি দেখবেন, তখন সেই সেলের মান হিসাব অনুযায়ী অটোমেটিক্যালি আপডেট হয়ে যাবে। তবে আপনি যদি Java কোডে Formula এর ফলাফল চান, তবে আপনাকে FormulaEvaluator ব্যবহার করতে হবে:

import org.apache.poi.ss.formula.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;

public class ReadFormulaResult {
    public static void main(String[] args) throws IOException {
        // Excel ফাইল লোড করা
        FileInputStream file = new FileInputStream("formulaExample.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        
        // Sheet এবং Row পাওয়া
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        
        // Formula Evaluator তৈরি করা
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        
        // Formula সেলের ফলাফল পাওয়া
        Cell formulaCell = row.getCell(2);  // C1 সেল
        System.out.println("Formula Result: " + formulaEvaluator.evaluate(formulaCell).getNumberValue());
        
        workbook.close();
        file.close();
    }
}

এখানে:

• FormulaEvaluator ব্যবহার করে Formula সেলের হিসাবকৃত ফলাফল বের করা হয়েছে।

2. অন্য ধরনের Functions এবং Formulas

Excel এ অনেক ধরনের বিল্ট-ইন Functions রয়েছে যেমন SUM, AVERAGE, IF, VLOOKUP, ইত্যাদি। আপনি এই Functions গুলোকেও Apache POI এর মাধ্যমে ব্যবহার করতে পারেন। নিচে কিছু সাধারণ Function এর উদাহরণ দেওয়া হল:

2.1 SUM Function

// C1 সেলে A1 এবং B1 এর যোগফল (SUM Function)
cell3.setCellFormula("SUM(A1:B1)");

2.2 AVERAGE Function

// C1 সেলে A1 থেকে A5 পর্যন্ত মানের গড় (AVERAGE Function)
cell3.setCellFormula("AVERAGE(A1:A5)");

2.3 IF Function

// C1 সেলে A1 এর মান যদি 10 এর বেশি হয় তবে "Pass" নয়তো "Fail" লিখবে (IF Function)
cell3.setCellFormula("IF(A1>10, \"Pass\", \"Fail\")");

2.4 VLOOKUP Function

// C1 সেলে A1 এর মানের সাথে মেলানো B1 থেকে B5 পর্যন্ত মানের সাথে মিলিয়ে দেখবে (VLOOKUP Function)
cell3.setCellFormula("VLOOKUP(A1, B1:B5, 1, FALSE)");

3. ফর্মুলার জন্য রেফারেন্স ব্যবস্থাপনা

যখন আপনি ফর্মুলা ব্যবহার করবেন, তখন সেলের মধ্যে সঠিকভাবে রেফারেন্স দেওয়ার বিষয়টি খুব গুরুত্বপূর্ণ। Excel ফরম্যাটে, সেলের রেফারেন্স একটি নির্দিষ্ট প্যাটার্ন অনুসরণ করে (যেমন A1, B2 ইত্যাদি)। POI এর মাধ্যমে আপনি ডাইনামিক্যালি সেলের রেফারেন্সের পরিবর্তন করতে পারেন এবং সেই রেফারেন্সগুলিকে Formula এর মধ্যে ব্যবহার করতে পারেন।

4. Error Handling for Formulas

Excel ফাইলের মধ্যে Formula নির্ধারণ করতে গিয়ে ভুল রেফারেন্স বা ইনভ্যালিড ফর্মুলা থাকলে, আপনি FormulaEvaluator এর মাধ্যমে এর ফলাফল চেক করতে পারবেন এবং প্রয়োজনে ত্রুটি ধরতে পারবেন।

FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell cell = row.getCell(2);  // Formula সেল
try {
    double result = formulaEvaluator.evaluate(cell).getNumberValue();
    System.out.println("Formula Result: " + result);
} catch (Exception e) {
    System.out.println("Error in Formula: " + e.getMessage());
}

সারাংশ

Apache POI লাইব্রেরি দিয়ে আপনি Excel ফাইলের মধ্যে Formula এবং Function ব্যবহার করে ডেটার উপর গণনা বা প্রক্রিয়াকরণ করতে পারেন। আপনি Formula এর মাধ্যমে গাণিতিক হিসাব যেমন যোগফল, গড়, IF স্টেটমেন্ট, VLOOKUP ইত্যাদি ব্যবহার করতে পারবেন। এছাড়া, FormulaEvaluator ব্যবহার করে Formula এর ফলাফলও পাওয়া যাবে।

• Formula এবং Function ব্যবহার করতে setCellFormula মেথড ব্যবহার করা হয়।
• FormulaEvaluator ব্যবহার করে Formula এর ফলাফল পড়া যায়।

এটি আপনাকে Excel ফাইলের শক্তিশালী গাণিতিক ও লজিক্যাল প্রসেসিং করতে সক্ষম করবে।